#!/bin/bash

# 数据库恢复脚本 (Linux/macOS)
# Database Restore Script for Delivery Management System

set -e

# 配置
DB_PATH="../data/delivery-management.db"
BACKUP_PATH="./backup/full-backup.sql"
SCHEMA_PATH="./schema/schema.sql"
DATA_DIR="./data"

# 颜色输出
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

echo -e "${BLUE}=== 交付管理系统数据库恢复脚本 ===${NC}"
echo -e "${BLUE}Delivery Management System Database Restore Script${NC}"
echo ""

# 检查sqlite3是否安装
if ! command -v sqlite3 &> /dev/null; then
    echo -e "${RED}错误: sqlite3 未安装。请先安装 sqlite3。${NC}"
    echo -e "${RED}Error: sqlite3 not found. Please install sqlite3 first.${NC}"
    exit 1
fi

# 显示选项菜单
echo "请选择恢复方式 / Please choose restore method:"
echo "1) 完整恢复 (删除现有数据库并从完整备份恢复)"
echo "   Full restore (delete existing database and restore from full backup)"
echo "2) 仅恢复结构 (创建空数据库结构)"
echo "   Schema only (create empty database structure)"
echo "3) 仅恢复核心数据 (恢复用户、角色、元数据等核心表)"
echo "   Core data only (restore users, roles, metadata tables)"
echo "4) 自定义恢复 (选择特定数据文件)"
echo "   Custom restore (choose specific data files)"
echo ""

read -p "请输入选项 (1-4) / Enter option (1-4): " choice

case $choice in
    1)
        echo -e "${YELLOW}执行完整恢复...${NC}"
        echo -e "${YELLOW}Performing full restore...${NC}"
        
        # 备份现有数据库
        if [ -f "$DB_PATH" ]; then
            echo "备份现有数据库..."
            cp "$DB_PATH" "${DB_PATH}.backup.$(date +%Y%m%d_%H%M%S)"
        fi
        
        # 删除现有数据库
        rm -f "$DB_PATH"
        
        # 从完整备份恢复
        echo "从完整备份恢复数据库..."
        sqlite3 "$DB_PATH" < "$BACKUP_PATH"
        
        echo -e "${GREEN}完整恢复完成！${NC}"
        ;;
        
    2)
        echo -e "${YELLOW}仅创建数据库结构...${NC}"
        echo -e "${YELLOW}Creating database schema only...${NC}"
        
        # 备份现有数据库
        if [ -f "$DB_PATH" ]; then
            echo "备份现有数据库..."
            cp "$DB_PATH" "${DB_PATH}.backup.$(date +%Y%m%d_%H%M%S)"
        fi
        
        # 删除现有数据库
        rm -f "$DB_PATH"
        
        # 仅恢复结构
        sqlite3 "$DB_PATH" < "$SCHEMA_PATH"
        
        echo -e "${GREEN}数据库结构创建完成！${NC}"
        ;;
        
    3)
        echo -e "${YELLOW}恢复核心数据...${NC}"
        echo -e "${YELLOW}Restoring core data...${NC}"
        
        # 确保数据库存在（如果不存在则先创建结构）
        if [ ! -f "$DB_PATH" ]; then
            echo "数据库不存在，先创建结构..."
            sqlite3 "$DB_PATH" < "$SCHEMA_PATH"
        fi
        
        # 清空核心表
        echo "清空核心表..."
        sqlite3 "$DB_PATH" "DELETE FROM users; DELETE FROM roles; DELETE FROM parameter_field_metadata; DELETE FROM parameter_table_metadata; DELETE FROM devices;"
        
        # 恢复核心数据
        echo "恢复用户和角色数据..."
        sqlite3 "$DB_PATH" < "$DATA_DIR/roles.sql"
        sqlite3 "$DB_PATH" < "$DATA_DIR/users.sql"
        
        echo "恢复元数据..."
        sqlite3 "$DB_PATH" < "$DATA_DIR/parameter_field_metadata.sql"
        sqlite3 "$DB_PATH" < "$DATA_DIR/parameter_table_metadata.sql"
        
        echo "恢复设备数据..."
        sqlite3 "$DB_PATH" < "$DATA_DIR/devices.sql"
        
        echo -e "${GREEN}核心数据恢复完成！${NC}"
        ;;
        
    4)
        echo -e "${YELLOW}自定义恢复模式${NC}"
        echo -e "${YELLOW}Custom restore mode${NC}"
        echo ""
        echo "可用的数据文件 / Available data files:"
        
        ls -la "$DATA_DIR"/*.sql 2>/dev/null || echo "没有找到数据文件 / No data files found"
        
        echo ""
        echo "请输入要恢复的SQL文件名 (多个文件用空格分隔):"
        echo "Enter SQL filenames to restore (separate multiple files with spaces):"
        read -p "> " files
        
        # 确保数据库存在
        if [ ! -f "$DB_PATH" ]; then
            echo "数据库不存在，先创建结构..."
            sqlite3 "$DB_PATH" < "$SCHEMA_PATH"
        fi
        
        # 恢复指定文件
        for file in $files; do
            file_path="$DATA_DIR/$file"
            if [ -f "$file_path" ]; then
                echo "恢复 $file..."
                sqlite3 "$DB_PATH" < "$file_path"
            else
                echo -e "${RED}警告: 文件 $file 不存在${NC}"
            fi
        done
        
        echo -e "${GREEN}自定义恢复完成！${NC}"
        ;;
        
    *)
        echo -e "${RED}无效选项${NC}"
        exit 1
        ;;
esac

# 检查数据库完整性
echo ""
echo "检查数据库完整性..."
if sqlite3 "$DB_PATH" "PRAGMA integrity_check;" | grep -q "ok"; then
    echo -e "${GREEN}数据库完整性检查通过！${NC}"
else
    echo -e "${RED}数据库完整性检查失败！${NC}"
    exit 1
fi

# 显示数据库统计信息
echo ""
echo -e "${BLUE}数据库统计信息 / Database Statistics:${NC}"
echo "数据库文件大小 / Database file size: $(du -h "$DB_PATH" | cut -f1)"

# 显示表数量
table_count=$(sqlite3 "$DB_PATH" ".tables" | wc -w)
echo "表数量 / Number of tables: $table_count"

# 显示用户数量
user_count=$(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM users;" 2>/dev/null || echo "0")
echo "用户数量 / Number of users: $user_count"

# 显示设备数量
device_count=$(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM devices;" 2>/dev/null || echo "0")
echo "设备数量 / Number of devices: $device_count"

echo ""
echo -e "${GREEN}数据库恢复操作完成！${NC}"
echo -e "${GREEN}Database restore operation completed!${NC}"